package cn.lili.modules.promotion.mapper;

import cn.lili.modules.order.order.entity.dos.Order;
import cn.lili.modules.order.order.entity.vo.UnionMonthFundsVO;
import cn.lili.modules.promotion.entity.dos.GreenContributionInfo;
import cn.lili.modules.promotion.entity.vos.GreenContributionInfoViewVO;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;

/**
 * @author xiangqian
 * @version 1.0
 * @data 2023/12/18 14:14
 */
public interface GreenContributionInfoMapper extends BaseMapper<GreenContributionInfo> {

    /**
     * 查询我的待结算绿色贡献值
     * */
    @Select("select IFNULL(sum(score_number),0) from li_green_contribution_info where status = 'TO_BE_SETTLED' and user_id = #{userId}")
    BigDecimal getUmber(@Param("userId")String userId);


    /**
     * 工会贡献值明细
     * @param info
     * @param page
     * @return
     */
    @Select(" SELECT un.id,info.user_id,info.union_id,info.status,info.type," +
            "info.umber,info.score_number,info.after_balance,info.create_time, info.order_sn " +
            " FROM lilishop_promotion.li_green_contribution_info AS info " +
            " JOIN lilishop_user.li_operation_center AS un ON info.user_id = un.user_id ${ew.customSqlSegment} ")
            //" WHERE info.`status` = #{info.status} AND info.type = #{info.type} AND info.user_type = #{info.userType} AND info.user_id = #{info.userId} order by info.create_time desc")
    Page<GreenContributionInfoViewVO> getUnionContributeDetails(@Param(Constants.WRAPPER) Wrapper queryWrapper, Page page);

    /**
     * 统计贡献值总量
     * @param info
     * @return
     */
    @Select(" SELECT " +
            " IFNULL(sum(info.umber),0) AS umber " +
            " FROM " +
            " lilishop_promotion.li_green_contribution_info AS info " +
            " WHERE " +
            " info.`status` = #{info.status} AND info.type = #{info.type} AND info.user_id = #{info.userId}")
    Double getSumContributeSettle(@Param("info") GreenContributionInfo info);


    @Select(" select DATE_FORMAT(create_time, '%Y/%m' ) as yearMonth, " +
            " SUM(CASE WHEN `status` = 'TO_BE_SETTLED' THEN score_number END) as unSettlementPrice," +
            " SUM(CASE WHEN `status` = 'DIRECT' THEN score_number END) as settlementGreenScore, " +
            " SUM(CASE WHEN `status` = 'INCOME' THEN umber END) as settlementUmber " +
            " from li_green_contribution_info " +
            " where union_id = #{unionId}")
    Page<UnionMonthFundsVO> getSumContributeSettleByUnion(Page page,@Param("unionId") String unionId);

}
